package com.efast.cafe.commponent.crud.service.impl;

import java.io.ByteArrayOutputStream;
import java.io.PrintStream;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang.ObjectUtils;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Component;

import com.efast.cafe.framework.exception.ServiceException;
import com.efast.cafe.framework.jdbc.JdbcDao;
import com.efast.cafe.framework.jdbc.persistence.DefaultNameHandler;
import com.efast.cafe.framework.jdbc.persistence.NameHandler;
import com.efast.cafe.framework.util.SpringWebUtils;
import com.efast.cafe.portal.bean.common.DateValidationBean;
import com.efast.cafe.portal.bean.common.DoubleValidationBean;
import com.efast.cafe.portal.bean.common.EntityFieldDetailBean;
import com.efast.cafe.portal.bean.common.EntityFieldInfoBean;
import com.efast.cafe.portal.bean.common.IntValidationBean;
import com.efast.cafe.portal.bean.common.StringValidationBean;
import com.efast.cafe.util.StringUtil;
import com.efast.cafe.util.StringValidationUtils;
import com.efast.cafe.util.date.DateUtils;
import com.efast.cafe.util.date.LocalDateUtils;

import net.sf.json.JSONObject;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
@Component
public class CrudSqlGenerateUtils {

	protected static NameHandler nameHandler;
	static {
		if (nameHandler == null) {
			nameHandler = new DefaultNameHandler();
		}
	}
	
	public static String FIELD_CAN_NOT_BE_EMPTY="crud.error.field.empty";//字段不能为空国际化key
	public static String FIELD_MIN_LENGTH_ERROR="crud.error.field.minlength";//最小长度校验失败
	public static String FIELD_MAX_LENGTH_ERROR="crud.error.field.maxlength";//最大长度校验失败
	public static String FIELD_CHECK_TYPE_ERROR="crud.error.field.check";//校验不通过
	public static String FIELD_MINVALUE_ERROR="crud.error.field.min";//最小值校验失败
	public static String FIELD_MAXVALUE_ERROR="crud.error.field.max";//最小值校验失败
	public static String FIELD_UNIQUE_ERROR="crud.error.unique";//唯一校验不通过
	public static String FIELD_IMPORT_DI="crud.import.di";//第
	public static String FIELD_IMPORT_ROW="crud.import.row";//行
	public static String FIELD_IMPORT_COLUMN="crud.import.row";//列
	public static String FIELD_IMPORT_SHEET="crud.import.sheet";//工作表
	
	@Autowired
	@Qualifier("jdbcDao")
	private JdbcDao jdbcDao;
	
	/** 日志对象 */
	@SuppressWarnings("unused")
	private  final Logger LOG = LoggerFactory.getLogger(CrudSqlGenerateUtils.class);

	private void fieldValida(StringBuilder sqlBuilder, Object paramValue, EntityFieldInfoBean info, String columnName ,List<Object> params) {
		EntityFieldDetailBean detail = info.getField();
		// 字段类型.1:string,2:int,3:double,4:date 5:boolean
		if (detail.getFieldType().intValue() == 1) {
			// 非空校验
			if (detail.getRequired() == 1 && (paramValue == null || StringUtils.isBlank(paramValue.toString()))) {
				throw new ServiceException(FIELD_CAN_NOT_BE_EMPTY,detail.getFieldName());
			}
			if (StringUtils.isNotBlank(info.getValidation())) {
				JSONObject jo = JSONObject.fromObject(info.getValidation());
				StringValidationBean validationBean = (StringValidationBean) JSONObject.toBean(jo, StringValidationBean.class);
				if (validationBean.getMinLength() != null) {
					if (paramValue != null && paramValue.toString().length() < validationBean.getMinLength().intValue()) {
						throw new ServiceException(FIELD_MIN_LENGTH_ERROR,detail.getFieldName());// 最小长度校验失败
					}
				}
				if (validationBean.getMaxLength() != null) {
					if (paramValue != null && paramValue.toString().length() > validationBean.getMaxLength().intValue()) {
						throw new ServiceException(FIELD_MAX_LENGTH_ERROR,detail.getFieldName());// 最大长度校验失败
					}
				}
				if (validationBean.getCheckType() != null) {
					for (String checkType : validationBean.getCheckType()) {
						if (paramValue == null || !checkString(checkType, ObjectUtils.toString(paramValue))) {
							throw new ServiceException(FIELD_CHECK_TYPE_ERROR,detail.getFieldName());
						}
					}
				}
				if (StringUtils.isNotBlank(validationBean.getRegx())) {
				}
			}
			sqlBuilder.append(columnName).append(" = ? , ");
			params.add(paramValue);
		}

		if (detail.getFieldType().intValue() == 2) {// 校验int类型数据
			// 非空校验
			if (detail.getRequired() == 1 && paramValue == null) {
				throw new ServiceException(FIELD_CAN_NOT_BE_EMPTY,detail.getFieldName());
			}
			if (StringUtils.isNotBlank(info.getValidation())) {
				JSONObject jo = JSONObject.fromObject(info.getValidation());
				IntValidationBean validationBean = (IntValidationBean) JSONObject.toBean(jo,
				    IntValidationBean.class);
				if (validationBean.getMin() != null && paramValue != null) {
					Integer intParamValue = Integer.parseInt(paramValue.toString());
					if (intParamValue.intValue() < validationBean.getMin().intValue()) {
						// 最小值校验不通过
						throw new ServiceException(FIELD_MINVALUE_ERROR,detail.getFieldName());
					}
				}
				if (validationBean.getMax() != null && paramValue != null) {
					Integer intParamValue = Integer.parseInt(paramValue.toString());
					if (intParamValue.intValue() > validationBean.getMax().intValue()) {
						// 最大值校验不通过
						throw new ServiceException(FIELD_MAXVALUE_ERROR,detail.getFieldName());
					}
				}
			}
			sqlBuilder.append(columnName).append(" = ? , ");
			params.add(paramValue != null ? Integer.parseInt(paramValue.toString()) : null);
		}

		if (detail.getFieldType().intValue() == 3) {
			// 非空校验
			if (detail.getRequired() == 1 && paramValue == null) {
				throw new ServiceException(FIELD_CAN_NOT_BE_EMPTY,detail.getFieldName());
			}
			if (StringUtils.isNotBlank(info.getValidation())) {
				JSONObject jo = JSONObject.fromObject(info.getValidation());
				DoubleValidationBean validationBean = (DoubleValidationBean) JSONObject.toBean(jo,
				    DoubleValidationBean.class);
				if (validationBean.getLength() != null && paramValue != null) {
					if (paramValue.toString().length() > validationBean.getLength().intValue()) {
						// double类型length总长度校验不通过
						throw new ServiceException(FIELD_CHECK_TYPE_ERROR,detail.getFieldName());
					}
				}
				if (validationBean.getPrecision() != null && paramValue != null) {
					if (paramValue.toString().indexOf(".") != -1
					    && paramValue.toString()
					        .substring(paramValue.toString().indexOf(".") + 1,
					            paramValue.toString().length())
					        .length() > validationBean.getPrecision().intValue()) {
						// double类型precision 小数点后几位校验不通过
						throw new ServiceException(FIELD_CHECK_TYPE_ERROR,detail.getFieldName());
					}
				}
				if (validationBean.getMin() != null && paramValue != null) {
					Double doubleValue = Double.parseDouble(paramValue.toString());
					if (doubleValue.doubleValue() < validationBean.getMin().doubleValue()) {
						// double类型 min 最小值校验不通过
						throw new ServiceException(FIELD_MINVALUE_ERROR,detail.getFieldName());
					}
				}
				if (validationBean.getMax() != null && paramValue != null) {
					Double doubleValue = Double.parseDouble(paramValue.toString());
					if (doubleValue.doubleValue() > validationBean.getMax().doubleValue()) {
						// double类型 max 最小值校验不通过
						throw new ServiceException(FIELD_MAXVALUE_ERROR,detail.getFieldName());
					}
				}
			}
			sqlBuilder.append(columnName).append(" = ? , ");
			params.add(paramValue != null ? Double.parseDouble(paramValue.toString()) : null);
		}

		if (detail.getFieldType().intValue() == 4) {
			// 非空校验
			if (detail.getRequired() == 1 && paramValue == null) {
				throw new ServiceException(FIELD_CAN_NOT_BE_EMPTY,detail.getFieldName());
			}
			if (StringUtils.isNotBlank(info.getValidation())) {
				JSONObject jo = JSONObject.fromObject(info.getValidation());
				DateValidationBean validationBean = (DateValidationBean) JSONObject.toBean(jo,
				    DateValidationBean.class);
				if (StringUtils.isNotBlank(validationBean.getFormat()) && paramValue != null) {
					sqlBuilder.append(columnName).append(" = ? , ");
					params.add(paramValue != null ? DateUtils.format(DateUtils.parse(paramValue.toString(),validationBean.getFormat()), validationBean.getFormat()) : null);
					
				}
			} else {
				if (paramValue != null) {
					sqlBuilder.append(columnName).append(" = ? , ");
					params.add(paramValue != null ? DateUtils.format(DateUtils.parse(paramValue.toString(),DateUtils.FORMAT_DATE_YYYY_MM_DD_HH_MM_SS), DateUtils.FORMAT_DATE_YYYY_MM_DD_HH_MM_SS) : null);
				}
			}
		}

		if (detail.getFieldType().intValue() == 5) {
			// 非空校验
			if (detail.getRequired() == 1 && paramValue == null) {
				throw new ServiceException(FIELD_CAN_NOT_BE_EMPTY,detail.getFieldName());
			}
			sqlBuilder.append(columnName).append(" = ? , ");
			params.add(paramValue != null ? Integer.parseInt(paramValue.toString()) : null);
		}
		// file 不处理
		if (detail.getFieldType().intValue() == 6) {

		}

		// json or entity
		if (detail.getFieldType().intValue() == 7 || detail.getFieldType().intValue() == 8) {
			sqlBuilder.append(columnName).append(" = ? , ");
			params.add(paramValue != null ? paramValue.toString() : null);
		}
		
		//time
		if (detail.getFieldType().intValue() == 9) {
			sqlBuilder.append(columnName).append(" = ? , ");
			params.add(paramValue != null ? paramValue.toString() : null);
		}
	}

	/**
	 * 构建insert语句
	 * 
	 * @param tableName
	 * @param params
	 * @return
	 */
	public String buildInsertSql(String tableName, Map<String, Object> param, List<EntityFieldInfoBean> fieldInfoBeans,List<EntityFieldInfoBean> extfieldInfoBeans, boolean camelCase, List<Object> params) {
		StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append(" insert \n into \n").append(tableName).append("\n ");
		sqlBuilder.append(" set \n");
		List<String> uniqueColumnNames = new ArrayList<String>();
		List<Object> uniqueParams = new ArrayList<>();
		StringBuilder checkUniqueSql = new StringBuilder("select * from "+tableName+" where 1=1 ");
		// 校验
		for (EntityFieldInfoBean info : fieldInfoBeans) {
			EntityFieldDetailBean detail = info.getField();
			boolean containsKey = param.containsKey(detail.getFieldName());
			if ((detail.getIsPk() != null && detail.getIsPk().intValue() == 1) || !containsKey) {
				continue;// 不校验
			}
			
			
			String columnName =StringUtil.isEmpty(detail.getPhysicalField())? detail.getFieldName() : detail.getPhysicalField();
			// 处理驼峰
			if (camelCase) {
				columnName = nameHandler.getColumnName(detail.getFieldName(), camelCase);
			}
			
			Object paramValue = param.get(detail.getFieldName());
			if (detail.getFieldName().equals("companyCode")) {
				sqlBuilder.append(columnName).append(" = ? , ");
				params.add(SpringWebUtils.getRequestCompany());
				continue;
			}
			if (detail.getFieldName().equals("createUser")) {
				sqlBuilder.append(columnName).append(" = ? , ");
				params.add(SpringWebUtils.getRequestUser());
				continue;
			}
			if (detail.getFieldName().equals("createTime") || "lastUpdateTime".equals(detail.getFieldName())) {
				sqlBuilder.append(columnName).append(" = ? , ");
				params.add(DateUtils.getCurrentDateAsString("yyyy-MM-dd HH:mm:ss"));
				continue;
			}
			
			if(detail.getUniqueIndex()==1 && (paramValue != null)){//校验唯一
				checkUniqueSql.append(" and "+columnName+"=?");
				if (detail.getFieldType().intValue() == 1) {
					uniqueParams.add(paramValue.toString());
				}
				if (detail.getFieldType().intValue() == 2) {
					uniqueParams.add(Integer.parseInt(paramValue.toString()));
				}
				uniqueColumnNames.add(detail.getFieldName());
			}
			
			{
				// 外键的校验
				// referEntity
				if (detail.getFieldType() == 6) {
					// XXX:待实现
					// 获取实体定义
				}
			}

			fieldValida(sqlBuilder, paramValue, info, columnName,params);
		}
		if(extfieldInfoBeans!=null && extfieldInfoBeans.size()>0){
			sqlBuilder.append("extAttrs").append(" = ?");
			params.add(param.get("extAttrs")!=null?param.get("extAttrs").toString():null);
		}
		
		if(uniqueParams.size()>0){
			int uniqueCount = jdbcDao.queryCount(checkUniqueSql.toString(),uniqueParams.toArray());
			if(uniqueCount>0){
				throw new ServiceException(FIELD_UNIQUE_ERROR,(String[])uniqueColumnNames.toArray(new String[uniqueColumnNames.size()]));
			}
		}

		// 去掉最后一个，
		String sql = sqlBuilder.toString();
		if(extfieldInfoBeans==null || extfieldInfoBeans.size()==0)
			sql = sql.substring(0, sql.length() - 2);
		return sql;
	}

	/**
	 * 构建update语句
	 * 
	 * @param tableName
	 * @param params
	 * @return
	 */
	public String buildUpdateSql(String tableName, Map<String, Object> param, List<EntityFieldInfoBean> fieldInfoBeans, List<EntityFieldInfoBean> extfieldInfoBeans, boolean camelCase, List<Object> params) {
		StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append(" update \n ").append(tableName).append("\n ");
		sqlBuilder.append(" set \n");
		StringBuilder updateWhereSql = new StringBuilder(" where 1 = 1  ");
		// 校验
		Boolean companyCode = false;
		String companyCodeColunmName = "";
		String pkColunmName = "";
		String pkFieldName = "id";
		
		List<String> uniqueColumnNames = new ArrayList<String>();
		List<Object> uniqueParams = new ArrayList<>();
		StringBuilder checkUniqueSql = new StringBuilder("select * from "+tableName+" where 1=1 ");
		
		for (EntityFieldInfoBean info : fieldInfoBeans) {
			EntityFieldDetailBean detail = info.getField();
			boolean containsKey = param.containsKey(detail.getFieldName());
			if ((detail.getIsPk() != null && detail.getIsPk().intValue() == 1)){
				pkFieldName = detail.getFieldName();
				pkColunmName = StringUtil.isEmpty(detail.getPhysicalField())? detail.getFieldName() : detail.getPhysicalField();
			}
			if ((detail.getIsPk() != null && detail.getIsPk().intValue() == 1) || !containsKey) {
				continue;// 不校验
			}
			Object paramValue = param.get(detail.getFieldName());
			String columnName = StringUtil.isEmpty(detail.getPhysicalField())? detail.getFieldName() : detail.getPhysicalField();
			// 处理驼峰
//			if (camelCase) {
//				columnName = nameHandler.getColumnName(detail.getFieldName(), camelCase);
//			}
//			if (detail.getFieldName().equals("companyCode")) {
//				sqlBuilder.append(columnName).append(" = ? , ");
//				params.add(SpringWebUtils.getRequestCompany());
//				continue;
//			}
			if (detail.getFieldName().equals("createUser")) {
				sqlBuilder.append(columnName).append(" = ? , ");
				params.add(SpringWebUtils.getRequestUser());
				continue;
			}
			if (detail.getFieldName().equals("createTime") || "lastUpdateTime".equals(detail.getFieldName())) {
				sqlBuilder.append(columnName).append(" = ? , ");
				params.add(DateUtils.getCurrentDateAsString("yyyy-MM-dd HH:mm:ss"));
				continue;
			}
			
			if(detail.getUniqueIndex()==1 && (paramValue != null && StringUtils.isNotBlank(paramValue.toString()))){//校验唯一
				checkUniqueSql.append(" and "+columnName+"=?");
				if (detail.getFieldType().intValue() == 1) {
					uniqueParams.add(paramValue.toString());
				}
				if (detail.getFieldType().intValue() == 2) {
					uniqueParams.add(Integer.parseInt(paramValue.toString()));
				}
				uniqueColumnNames.add(detail.getFieldName());
			}
			

			{
				// 外键的校验
				// referEntity
				if (StringUtils.isNotBlank(detail.getReferEntity())) {
					// XXX:待实现
					// 获取实体定义
				}
			}
			fieldValida(sqlBuilder, paramValue, info, columnName, params);

			if (detail.getFieldName().equals("companyCode")) {
				companyCode = true;
				companyCodeColunmName = columnName;
				continue;
			}
		}
		if(extfieldInfoBeans!=null && extfieldInfoBeans.size()>0){
			sqlBuilder.append("extAttrs").append(" = ?");
			params.add(param.get("extAttrs")!=null?param.get("extAttrs").toString():null);
		}
		
		if(uniqueParams.size()>0){
			checkUniqueSql.append(" and ").append(" "+(StringUtils.isNotBlank(pkColunmName)?pkColunmName:"id")+" ").append(" <> ? ");
			uniqueParams.add(param.get(pkFieldName)!=null? Integer.parseInt(param.get(pkFieldName).toString()):null);
			int uniqueCount = jdbcDao.queryCount(checkUniqueSql.toString(),uniqueParams.toArray());
			if(uniqueCount>0){
				throw new ServiceException(FIELD_UNIQUE_ERROR,(String[])uniqueColumnNames.toArray(new String[uniqueColumnNames.size()]));
			}
		}
		
		
		updateWhereSql.append(" and ").append(" "+(StringUtils.isNotBlank(pkColunmName)?pkColunmName:"id")+" ").append(" = ? ");
		pkFieldName = StringUtils.isNotBlank(pkFieldName)?pkFieldName:"id";
		params.add(param.get(pkFieldName)!=null? Integer.parseInt(param.get(pkFieldName).toString()):null);
		if (companyCode) {
			updateWhereSql.append(" and ").append(" "+companyCodeColunmName+" ").append(" = ? ");
			params.add(SpringWebUtils.getRequestCompany());
		}
		// 去掉最后一个，
		String sql = sqlBuilder.toString();
		if(extfieldInfoBeans==null || extfieldInfoBeans.size()==0)
			sql = sql.substring(0, sql.length() - 2);
		sql += updateWhereSql;
		doValidation(sql);
		return sql;
	}

	/**
	 * 构建insert语句
	 * 
	 * @param tableName
	 * @param params
	 * @return
	 */
	public  String buildDeleteSql(String tableName, Map<String, Object> param, List<EntityFieldInfoBean> fieldInfoBeans, List<Object> params) {
		StringBuilder sql = new StringBuilder("DELETE FROM " + tableName + " WHERE 1 = 1");
		sql.append(" and ");
//		for (Map.Entry<String, Object> p : param.entrySet()) {
//			Object value = p.getValue();
//			if (value != null && value instanceof Collection) {
//				// XXX:处理多条,暂时不实现
//			} else {
//				sql.append(" ").append(p.getKey()).append(" = ?");
//				params.add(p.getValue()!=null? Integer.parseInt(p.getValue().toString()):null);
//			}
//		}
 
		for (EntityFieldInfoBean info : fieldInfoBeans) {
			EntityFieldDetailBean detail = info.getField();
			String colunm = StringUtil.isEmpty(detail.getPhysicalField())? detail.getFieldName() : detail.getPhysicalField();
			if(detail.getIsPk().intValue()==1){
				sql.append(" ").append(colunm).append(" = ?");
				if(param.containsKey(detail.getFieldName())){
					params.add(Integer.parseInt(param.get(detail.getFieldName()).toString()));
				}else{
					params.add(Integer.parseInt(param.get("id").toString()));
				}
			}
//			if (detail.getFieldName().equals("companyCode")) {
//				sql.append(" and ");
//				sql.append(" "+colunm+" ").append(" = ?");
//				params.add(SpringWebUtils.getRequestCompany());
//			}
		}
		doValidation(sql.toString());
//		aaaaa;
		return sql.toString();
	}

	public  String buildFindSql(String tableName, Map<String, Integer> map, List<EntityFieldInfoBean> fieldInfoBeans, List<EntityFieldInfoBean> extfieldInfoBeans, boolean camelCase, List<Object> params) {
		
		StringBuilder sqlBuilder = new StringBuilder("select ");
		String findWhereSql = " where ";
		int i=0;
		for (EntityFieldInfoBean info : fieldInfoBeans) {
			EntityFieldDetailBean detail = info.getField();
			String columnName = StringUtil.isEmpty(detail.getPhysicalField())? detail.getFieldName() : detail.getPhysicalField();
			String fieldName = StringUtil.isEmpty(detail.getFieldName())? detail.getPhysicalField() : detail.getFieldName();
			// 处理驼峰,以别名的方式适应前端取字段
			if (camelCase) {
				// columnName = nameHandler.getColumnName(detail.getFieldName(),
				// camelCase);
			}
			if(info.getField().getFieldType()!=6){
				sqlBuilder.append(" "+columnName ).append(" as "+fieldName);
				if(i!=fieldInfoBeans.size()-1){
					sqlBuilder.append(",");
				}
			}
			
			
			// sqlBuilder.append(columnName).append(" , ");
			{
				// 查出主键作为查询的条件
				// 查出主键作为更新的条件
				if (detail.getIsPk() == 1) {
					findWhereSql += columnName + " = ? " ;
					params.add(map.get(detail.getFieldName()));
				}

			}
			i++;
		}
		if(extfieldInfoBeans!=null && extfieldInfoBeans.size()>0){
			sqlBuilder.append(",extAttrs");
		}
		//如果sql最后一个字符是逗号,则去掉
		if(sqlBuilder.substring(sqlBuilder.length() - 1,sqlBuilder.length()).equals(",")){
			sqlBuilder.deleteCharAt(sqlBuilder.length()-1);
		}
		sqlBuilder.append(" from ").append(tableName);
		// 去掉最后一个，
		String sql = sqlBuilder.toString();
		sql += findWhereSql;
		doValidation(sql);
		return sql;
	}

	public  boolean checkString(String checkType, String value) {
		boolean check = true;
		switch (checkType) {
		case "emailCheck":// 邮箱校验
			check = StringValidationUtils.checkEmail(value);
			break;
		case "phoneCheck":// 固话校验
			// 区号+座机号码+分机号码
			check = StringValidationUtils.checkPhone(value);
			break;
		case "mobileCheck":
			check = StringValidationUtils.checkMobile(value);
			break;
		case "urlCheck":
			check = StringValidationUtils.checkURL(value);
			break;
		case "ipv4Check":
			check = StringValidationUtils.checkIPV4(value);
			break;
		case "ipv6Check":
			check = StringValidationUtils.checkIPV6(value);
			break;
		case "cardCheck":
			check = StringValidationUtils.checkCard(value);
			break;
		case "qqCheck":
			check = StringValidationUtils.checkQQ(value);
			break;
		case "bankNumCheck":
			check = StringValidationUtils.checkBankNum(value);
			break;
		case "plateNumber":
			check = StringValidationUtils.checkPlate(value);
			break;
		case "chinaZipCheck":
			check = StringValidationUtils.checkChinaZip(value);
			break;
		case "englishCheck":
			check = StringValidationUtils.checkEnglish(value);
			break;
		case "chineseCheck":
			check = StringValidationUtils.checkChinese(value);
			break;
		case "numCheck":
			check = StringValidationUtils.checkNumber(value);
			break;
		}
		return check;
	}

	public  void doValidation(String sql) {

		try {
			CCJSqlParserUtil.parse(sql);
		} catch (JSQLParserException e) {
			ByteArrayOutputStream baos = new ByteArrayOutputStream();
			e.printStackTrace(new PrintStream(baos));
			String exception = baos.toString();
			String regEx = "Encountered(.*)";
			Pattern pat = Pattern.compile(regEx);
			Matcher mat = pat.matcher(exception);
			while (mat.find()) {
				exception = mat.group(1);
			}
			String line = "";
			String regEx2 = "line (.*),";
			pat = Pattern.compile(regEx2);
			mat = pat.matcher(exception);
			while (mat.find()) {
				line = mat.group(1);
			}
			int line_num = Integer.valueOf(line).intValue();
			int indexofcolumn = exception.indexOf("column");
			String errornumber = exception.substring(indexofcolumn + 7,
			    exception.length() - 1);
			int error_num = Integer.valueOf(errornumber).intValue();

			String ERROR_location = "";
			if (error_num != 1) {
				String sql_sub = sql.substring(0, error_num - 2); // 发生错误位置前面的字符串
				// 错误信息单词往往处于错误位置的前一个地方单词
				// 获取错误位置两个前面两个空格之间的单词，并保存
				sql_sub = new StringBuilder(sql_sub).reverse().toString();
				int indexofspace = sql_sub.indexOf(" ");
				String sql_error = sql_sub.substring(0, indexofspace);
				sql_error = new StringBuilder(sql_error).reverse().toString();
				ERROR_location = "\"" + sql_error + "\"" + " at line "
				    + line_num + " at column " + error_num;
			} else {
				int indexofspace = sql.indexOf(" ");
				String sql_error = sql.substring(0, indexofspace);
				ERROR_location = "\"" + sql_error + "\"" + " at line "
				    + line_num + " at column " + error_num;
			}
			throw new ServiceException(ERROR_location); // 错误信息的返回
		}
	}


}
